﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Globalization;
using ContractManagementSystem.Entity;

namespace ContractManagementSystem.DataAccess
{
    public class UserDataAccess : LogDataAccess
    {
        private const string UserName = "Admin";

        /// <summary>
        /// Function Insert User
        /// Author: Group2.VietNA
        /// </summary>
        /// <param name="userEntity"></param>
        /// <returns>
        /// 1 if success.0 if fail.
        /// </returns>
        public string Insert(UserEntity userEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            string result;
            try
            {
                //log event          
                WriteEvent("Inserting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_Insert", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    Guid newId = Guid.NewGuid();
                    sqlCommand.Parameters.AddWithValue("userId", newId);
                    sqlCommand.Parameters.AddWithValue("@roleId", userEntity.RoleId);
                    sqlCommand.Parameters.AddWithValue("@userName", userEntity.UserName);
                    sqlCommand.Parameters.AddWithValue("@passWord", userEntity.Password);

                    sqlCommand.ExecuteNonQuery();
                    result = newId.ToString();
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Insert success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            catch (NullReferenceException nullReferenceException)
            {
                ErrorMessage = nullReferenceException.Message;

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Update User
        /// Author: Group2.VietNA
        /// </summary>
        /// <param name="userEntity"></param>
        /// <returns>
        /// 1 if success.0 if fail.
        /// </returns>
        public int Update(UserEntity userEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return 0;
            }

            int result = 0;
            try
            {
                //log event                
                WriteEvent("Updating...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_Update", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", userEntity.UserId);
                    sqlCommand.Parameters.AddWithValue("@roleId", userEntity.RoleId);
                    sqlCommand.Parameters.AddWithValue("@passWord", userEntity.Password);

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;

                    //log event
                    WriteEvent("Update success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            catch (NullReferenceException nullReferenceException)
            {
                ErrorMessage = nullReferenceException.Message;

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Delete User
        /// Author: Group2.VietNA
        /// </summary>
        /// <param name="id"></param>
        /// <returns>
        /// 1 if success.0 if fail.
        /// </returns>
        public new int Delete(string id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return 0;
            }

            int result = 0;
            try
            {
                //log event                
                WriteEvent("Deleting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_Delete", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@id", id));

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Delete success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Delete fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Select User
        /// Author: Group2.VietNA
        /// </summary>
        /// <returns>
        /// not null if success.null if faill
        /// </returns>
        public DataTable FetchAll(int type)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("UserId", typeof(Guid));
            dataTable.Columns.Add("RoleId", typeof(Guid));
            dataTable.Columns.Add("RoleName", typeof(string));
            dataTable.Columns.Add("UserName", typeof(string));
            dataTable.Columns.Add("CreateDate", typeof(DateTime));
            dataTable.Columns.Add("IsAssign", typeof(bool));
            dataTable.Columns.Add("IsActive", typeof(bool));
            dataTable.Columns.Add("STT", typeof(int));
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_Select", CreateConnection()))
                {
                    int i = 0;
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@type", type);

                    var sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        while (sqlDataReader.Read())
                        {
                            var userId = new Guid(sqlDataReader["UserId"].ToString());
                            var roleId = new Guid(sqlDataReader["RoleId"].ToString());
                            var roleName = sqlDataReader["RoleName"].ToString();
                            var userName = sqlDataReader["UserName"].ToString();
                            var createDate = DateTime.Parse(sqlDataReader["CreateDate"].ToString(), CultureInfo.CurrentCulture);
                            var isAssign = sqlDataReader["IsAssign"].ToString();
                            var isActive = sqlDataReader["IsActive"].ToString();
                            if (i == 14)
                            {
                                i = 0;
                            }
                            dataTable.Rows.Add(userId, roleId, roleName, userName, createDate, isAssign, isActive, i);
                            i++;
                        }
                    }
                    ErrorMessage = null;
                    sqlDataReader.Close();
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return dataTable;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// Function SelectById User
        /// Author: Group2.VietNA
        /// </summary>
        /// <param name="userId"></param>
        /// <returns>
        /// not null if success.null if faill
        /// </returns>
        public DataTable FetchById(string userId)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            DataTable result = null;
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_SelectById", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", userId);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    result = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(result);
                    ErrorMessage = null;
                }
                //log event
                WriteEvent("Select success!", UserName);
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function ResetPass User
        /// Author: Group2.VietNA
        /// </summary>
        /// <param name="userId"></param>
        /// <returns>
        /// 1 if success.0 if fail.
        /// </returns>
        public int ResetPassword(string userId)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return 0;
            }

            int result = 0;
            try
            {
                //log event                
                WriteEvent("Reseting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_ResetPass", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", userId);

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Reset fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            //log event
            WriteEvent("Reset success!", UserName);

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public DataTable FetchByUserName(string userName)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("UserId", typeof(Guid));
            dataTable.Columns.Add("RoleId", typeof(Guid));
            dataTable.Columns.Add("RoleName", typeof(string));
            dataTable.Columns.Add("UserName", typeof(string));
            dataTable.Columns.Add("CreateDate", typeof(DateTime));
            dataTable.Columns.Add("IsAssign", typeof(bool));
            dataTable.Columns.Add("IsActive", typeof(bool));
            dataTable.Columns.Add("STT", typeof(int));
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_SelectByUserName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@username", userName);

                    int i = 0;
                    var sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        while (sqlDataReader.Read())
                        {
                            var userId = new Guid(sqlDataReader["UserId"].ToString());
                            var roleId = new Guid(sqlDataReader["RoleId"].ToString());
                            var roleName = sqlDataReader["RoleName"].ToString();
                            var uName = sqlDataReader["UserName"].ToString();
                            var createDate = DateTime.Parse(sqlDataReader["CreateDate"].ToString(), CultureInfo.CurrentCulture);
                            var isAssign = sqlDataReader["IsAssign"].ToString();
                            var isActive = sqlDataReader["IsActive"].ToString();
                            if (i == 14)
                            {
                                i = 0;
                            }
                            dataTable.Rows.Add(userId, roleId, roleName, uName, createDate, isAssign, isActive, i);
                            i++;
                        }
                    }
                    ErrorMessage = null;
                    sqlDataReader.Close();
                }
                //log event
                WriteEvent("Select success!", UserName);
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        public DataTable FetchByTopUserName(string userName)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("UserId", typeof(Guid));
            dataTable.Columns.Add("RoleId", typeof(Guid));
            dataTable.Columns.Add("RoleName", typeof(string));
            dataTable.Columns.Add("UserName", typeof(string));
            dataTable.Columns.Add("CreateDate", typeof(DateTime));
            dataTable.Columns.Add("IsAssign", typeof(bool));
            dataTable.Columns.Add("IsActive", typeof(bool));
            dataTable.Columns.Add("STT", typeof(int));
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_TopSelectByUserName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@username", userName);

                    int i = 0;
                    var sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        while (sqlDataReader.Read())
                        {
                            var userId = new Guid(sqlDataReader["UserId"].ToString());
                            var roleId = new Guid(sqlDataReader["RoleId"].ToString());
                            var roleName = sqlDataReader["RoleName"].ToString();
                            var uName = sqlDataReader["UserName"].ToString();
                            var createDate = DateTime.Parse(sqlDataReader["CreateDate"].ToString(), CultureInfo.CurrentCulture);
                            var isAssign = sqlDataReader["IsAssign"].ToString();
                            var isActive = sqlDataReader["IsActive"].ToString();
                            if (i == 14)
                            {
                                i = 0;
                            }
                            dataTable.Rows.Add(userId, roleId, roleName, uName, createDate, isAssign, isActive, i);
                            i++;
                        }
                    }
                    ErrorMessage = null;
                    sqlDataReader.Close();
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        public bool UpdateUserRole(UserEntity userEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            try
            {
                //log event                
                WriteEvent("Updating...", UserName);

                using (var sqlCommand = new SqlCommand("ap_User_UpdateRole", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@UserId", userEntity.UserId);
                    sqlCommand.Parameters.AddWithValue("@RoleId", userEntity.RoleId);
                    sqlCommand.Parameters.AddWithValue("@IsActive", userEntity.IsActive);

                    sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Update success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return false;
            }
            finally
            {
                CloseConnection();
            }

            return true;
        }

        public DataTable FetchByExactUserName(string userName)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            DataTable result;
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_SelectByExactUserName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@username", userName);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    result = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(result);
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        public string FetchRoleByUserName(string userName)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return "";
            }

            string result = "";
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_SelectRoleByName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@userName", userName);

                    result = sqlCommand.ExecuteScalar().ToString();
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            catch (NullReferenceException nullReferenceException)
            {
                ErrorMessage = nullReferenceException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        public string FetchPassword(string userName)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return "";
            }
            string result = "";
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_FetchPassword", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@userName", userName);

                    result = sqlCommand.ExecuteScalar().ToString();
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        public bool UpdatePassword(string password, string name)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            try
            {
                //log event                
                WriteEvent("Updating...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_UpdatePassword", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@UserName", name);
                    sqlCommand.Parameters.AddWithValue("@Password", password);

                    sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return false;
            }
            finally
            {
                CloseConnection();
            }

            //log event
            WriteEvent("Update success!", UserName);

            return true;
        }

        public string FetchIdByUserName(string userName)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            string result = null;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_profile_SelectIdByUserName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@userName", userName);

                    result = sqlCommand.ExecuteScalar().ToString();
                }
            }
            catch (NullReferenceException nullReferenceException)
            {
                ErrorMessage = nullReferenceException.Message;

                //log event
                WriteEvent("Select fail!, " + nullReferenceException.Message, UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            catch (SqlException exception)
            {
                ErrorMessage = exception.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        public string FetchUserNameById(Guid id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            string result = null;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_profile_SelectUserNameById", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", id);

                    result = sqlCommand.ExecuteScalar().ToString();
                }
            }
            catch (SqlException exception)
            {
                ErrorMessage = exception.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            catch (FormatException exception)
            {
                ErrorMessage = exception.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return null;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        public DataTable FetchNameRole()
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Role_FetchRoleName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    var sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                    sqlDataAdapter.Fill(dataTable);
                }
            }
            catch (SqlException exception)
            {
                ErrorMessage = exception.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public DataTable FetchProfileByUserName(string userName)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            DataTable dataTable = null;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_profile_SelectAllByUserName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@userName", userName);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(dataTable);

                }
            }
            catch (SqlException exception)
            {
                ErrorMessage = exception.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool CheckLogOn(string userName, string password)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }

            bool result = false;
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_User_CheckLogOn", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@userName", userName);
                    sqlCommand.Parameters.AddWithValue("@passWord", password);

                    var data = sqlCommand.ExecuteScalar();
                    if (data != null)
                    {
                        result = true;
                    }
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        public DataTable FetchContractorIdByUserName(string userName)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            DataTable dataTable = null;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_User_GetContractorIdByUserName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@UserName", userName);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(dataTable);

                }
            }
            catch (SqlException exception)
            {
                ErrorMessage = exception.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }
    }
}
